import org.apache.spark.sql.SparkSession
import org.apache.spark.{SparkConf, SparkContext}

object zk2 {
  def main(args: Array[String]): Unit = {
    val sparkConf = new SparkConf().setMaster("local[*]").setAppName("app")
    val session = SparkSession.builder().config(sparkConf).getOrCreate()
////创建SparkSQL上下文对象，加载广告投放数据，字段名称含义如上所述，生成DataFrame
    val aa = session.read.csv("data/ad-log.csv").toDF("platform_name", "event_type", "ad_id", "event_time", "client_ip", "device_id", "client_os")

    aa.createTempView("ad")
//显示Schema字段信息及控制台打印输出样本数据（至少10条
    session.sql("select * from ad").show(10)
    //使用SparkSQL对原始广告投放数据预处理，基于DSL编程，将字段中空值（或空字符串）替换为"unknown"
    val ab = session.sql("select nvl(platform_name,'unknown') as platform_name,nvl(event_type,'unknown') as event_type," +
      "nvl(ad_id,'unknown') as ad_id," +
      "nvl(event_time,'unknown') as event_time," +
      "nvl(client_ip,'unknown') as client_ip," +
      "nvl(device_id,'unknown') as device_id," +
      "nvl(client_os,'unknown') as client_os" +
      " from ad")

    ab.createTempView("tbl_ad_logs")

    session.sql("select * from tbl_ad_logs").show()

    //查询每天各个推广平台不同客户端操作系统的广告投放量，并打印输出控制台；
    session.sql("select event_time,client_os,sum(ad_id) from tbl_ad_logs group by event_time,client_os").show()

    //按照推广平台、设备类型和操作系统计用户在不同广告上的曝光数量、点击数据量和点击率，并打印输出控制台
    session.sql("select platform_name,device_id,client_os,count(event_type) as num1" +
      ",sum(if(event_type='click',1,0)) as num2," +
      "ceil(count(event_type)/sum(if(event_type='click',1,0))) as num3 from tbl_ad_logs group by platform_name,device_id,client_os").show()

    //查询统计每个推广平台Top5投放广告，按照点击量排序计算，结果打印输出控制台；
    session.sql("select platform_name,ad_id,event_type,event_type,count(*) from tbl_ad_logs where event_type='click' group by platform_name,ad_id,event_type order by count(*) limit 5").show()


    session.stop()


  }
}
